package com.xja.dao.impl;

import com.xja.connutil.ConnUtil;
import com.xja.dao.ZcyDao;
import com.xja.entity.Bills;
import com.xja.entity.Indent;
import com.xja.entity.Room;
import com.xja.entity.User;
import com.xja.entity.pojo.BillsAndRoom;
import com.xja.entity.pojo.UrByIndent;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author zcy
 * @create 2022-05-05 19:50
 */
public class ZcyDaoimpl implements ZcyDao {

    @Override
    public List<Room> findRoom() throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select * from room");
        ResultSet rs = ps.executeQuery();
        ArrayList<Room> list = new ArrayList<>();
        while (rs.next()){
            Room room = new Room();
            room.setRoomId(rs.getInt("room_id"));
            room.setRoomKind(rs.getString("room_kind"));
            room.setRoomPrice(rs.getInt("room_price"));
            room.setRoomFloor(rs.getInt("room_floor"));
            room.setRoomStatus(rs.getInt("room_status"));
            room.setRoomStatus(rs.getInt("room_status"));
            room.setRoomName(rs.getString("room_name"));
            list.add(room);
        }
        return list;
    }


    @Override
    public Integer addRoom(Room room) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("insert into room (room_kind, room_price, room_floor, room_status, room_name) \n" +
                "values (?,?,?,?,?)");
        ps.setString(1,room.getRoomKind());
        ps.setInt(2,room.getRoomPrice());
        ps.setInt(3,room.getRoomFloor());
        ps.setInt(4,room.getRoomStatus());
        ps.setString(5,room.getRoomName());
        int i = ps.executeUpdate();
        return i;
    }

    @Override
    public Room findRoomById(int updateId) throws SQLException {
        Connection conn = ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select * from room where room_id=?");
        ps.setInt(1,updateId);
        ResultSet rs = ps.executeQuery();
        Room room1 = new Room();
        while (rs.next()){
            room1.setRoomId(rs.getInt("room_id"));
            room1.setRoomKind(rs.getString("room_kind"));
            room1.setRoomPrice(rs.getInt("room_price"));
            room1.setRoomFloor(rs.getInt("room_floor"));
            room1.setRoomStatus(rs.getInt("room_status"));
            room1.setRoomStatus(rs.getInt("room_status"));
            room1.setRoomName(rs.getString("room_name"));
        }
        return room1;
    }

    @Override
    public Integer updateRoom(Room updateroom) throws SQLException {
        Connection getconn = ConnUtil.getConn();
        PreparedStatement ps = getconn.prepareStatement("update room set room_kind=?,room_price=?,room_floor=?,room_status=?,room_name=? where room_id=?");
        ps.setString(1, updateroom.getRoomKind());
        ps.setInt(2, updateroom.getRoomPrice());
        ps.setInt(3, updateroom.getRoomFloor());
        ps.setInt(4, updateroom.getRoomStatus());
        ps.setString(5, updateroom.getRoomName());
        ps.setInt(6, updateroom.getRoomId());
        int i = ps.executeUpdate();
        return i;
    }

    @Override
    public Integer deleteRoomById(int deleteId) throws SQLException {
        Connection getconn = ConnUtil.getConn();
        PreparedStatement ps = getconn.prepareStatement("delete from room where room_id=?");
        ps.setInt(1, deleteId);
        Integer i = ps.executeUpdate();
        return i;
    }

    @Override
    public List<UrByIndent> FindAllIndent() throws SQLException {
        Connection conn=ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select i.*,u.user_name,u.user_phone,u.user_level,user_card,r.room_kind,r.room_price,r.room_floor,r.room_name from indent i left join user u on i.user_id = u.user_id left join room r on i.room_id = r.room_id");
        ResultSet rs = ps.executeQuery();
        ArrayList<UrByIndent> list = new ArrayList<>();
        while (rs.next()){
            Indent indent = new Indent();
            indent.setIndentId(rs.getInt("indent_id"));
            indent.setUserId(rs.getInt("user_id"));
            indent.setRoomId(rs.getInt("room_id"));
            indent.setCheckTime(rs.getDate("check-in time"));
            indent.setDuratiom(rs.getInt("duration"));
            indent.setRemark(rs.getString("remark"));
            User user = new User();
            user.setUserId(indent.getUserId());
            user.setUserName(rs.getString("user_name"));
            user.setUserPhone(rs.getString("user_phone"));
            user.setUserLevel(rs.getInt("user_level"));
            user.setUserCard(rs.getString("user_card"));
            Room room = new Room();
            room.setRoomId(rs.getInt("room_id"));
            room.setRoomKind(rs.getString("room_kind"));
            room.setRoomPrice(rs.getInt("room_price"));
            room.setRoomFloor(rs.getInt("room_floor"));
            room.setRoomName(rs.getString("room_name"));
            UrByIndent urByIndent = new UrByIndent(user, room,indent);
            list.add(urByIndent);
        }
        return list;
    }

    @Override
    public Integer deleteIndentById(int deleteIndentById) throws SQLException {
        Connection conn=ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("delete from indent where indent_id=?");
        ps.setInt(1,deleteIndentById);
        Integer i = ps.executeUpdate();
        return i;
    }

    @Override
    public Integer updateIndent(Indent updateIndent) throws SQLException {
        Connection getconn = ConnUtil.getConn();
        PreparedStatement ps = getconn.prepareStatement("update indent set indent_id=?,user_id=?,room_id=?,`check-in time`=?,duration=?,remark=? where indent_id=?");
        ps.setInt(1, updateIndent.getIndentId());
        ps.setInt(2, updateIndent.getUserId());
        ps.setInt(3, updateIndent.getRoomId());
        ps.setDate(4,new java.sql.Date(updateIndent.getCheckTime().getTime()));
        ps.setInt(5, updateIndent.getDuratiom());
        ps.setString(6, updateIndent.getRemark());
        ps.setInt(7,updateIndent.getIndentId());
        int i = ps.executeUpdate();
        return i;

    }

    @Override
    public Integer updateStatusById(Integer roomId, Integer status) throws SQLException {
        Connection getconn = ConnUtil.getConn();
        PreparedStatement ps = getconn.prepareStatement("update room set room_status=? where room_id=?");
        ps.setInt(1, status);
        ps.setInt(2, roomId);
        Integer i = ps.executeUpdate();
        return i;
    }

    @Override
    public List<BillsAndRoom> findBroom() throws SQLException {
        Connection conn=ConnUtil.getConn();
        PreparedStatement ps = conn.prepareStatement("select b.*,r.room_kind from bills b left join room r on b.room_id = r.room_id");
        ResultSet rs = ps.executeQuery();
        ArrayList<BillsAndRoom> list = new ArrayList<>();
        while (rs.next()){
            Bills bills = new Bills();
            bills.setBillsId(rs.getInt("bills_id"));
            bills.setUserId(rs.getInt("user_id"));
            bills.setRoomId(rs.getInt("room_id"));
            bills.setSelectSum(rs.getString("select_sum"));
            bills.setDuration(rs.getInt("duration"));
            Room room = new Room();
            room.setRoomId(rs.getInt("room_id"));
            room.setRoomKind(rs.getString("room_kind"));
//            room.setRoomPrice(rs.getInt("room_price"));
//            room.setRoomFloor(rs.getInt("room_floor"));
//            room.setRoomStatus(rs.getInt("room_status"));
//            room.setRoomName(rs.getString("room_name"));
            BillsAndRoom billsAndRoom = new BillsAndRoom(bills, room);
            list.add(billsAndRoom);
        }
        return list;
    }
}
